package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Gui.Terminal;
import Model.Customer;

public class DBCustomer {
	
	private final String selectAll = "SELECT name, address, zipCode, city, CustomerID, phoneNo FROM Customer";
	
	private Connection con;
	
	public DBCustomer()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	public Customer getCustomer(String phoneNo)
	{
		Customer result = null;
		
		//TODO: limit error messages by null-checking
		
		//create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured while createing a statement");
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//execute statement
		try {
			stmt.execute(selectAll + " WHERE phoneNo = " + phoneNo);
		} catch (SQLException e) {
			Terminal.say("execute error", e);
		}
		
		//retrieve result
		ResultSet resSet = null;
		
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		if(resSet != null) {
			try {
				resSet.next();
			} catch (SQLException e) {
				Terminal.say("get result error", e);
			}
			
			try {
				String name = resSet.getString("name");
				String address = resSet.getString("address");
				String zipCode = resSet.getString("zipCode");
				String city = resSet.getString("city");
				String id = resSet.getString("CustomerID");
				
				result = new Customer(id, name, address, zipCode, city, phoneNo);
				
			} catch (SQLException e) {
				Terminal.say("build customer error", e);
			}
		}
		
		try {
			stmt.close();
		} catch (Exception e) {
			Terminal.say("close statement error", e);
		}
		
		return result;
	}
	
	public ArrayList<Customer> getAllCustomers()
	{
		ArrayList<Customer> result = new ArrayList<Customer>();
		
		//TODO: limit error messages by null-checking
		
		//create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured while createing a statement");
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//execute statement
		try {
			stmt.execute(selectAll);
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		//retrieve result
		ResultSet resSet = null;
	
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		try {
			while(resSet.next()) {
				String id = resSet.getString("CustomerID");
				String name = resSet.getString("name");
				String address = resSet.getString("address");
				String zipCode = resSet.getString("zipCode");
				String city = resSet.getString("city");
				String phoneNo = resSet.getString("phoneNo");
				
				result.add(new Customer(id, name, address, zipCode, city, phoneNo));
			}
			
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Stack trace: ");
			Terminal.say(e.getStackTrace().toString());
		}
		
		return result;
	}
	
	public boolean addCustomer(Customer customer)
	{
		//we assume all is going well
		boolean success = true;
		
		//create statement
		Statement stmt = null;
		
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to add Customer to DB", e);
			success = false;
		}
		 
		//execute statement
		String id = customer.getId();
		String name = customer.getName();
		String address = customer.getAddress();
		String zipCode = customer.getZipCode();
		String city = customer.getCity();
		String phone = customer.getPhoneNo();
		String statement = String.format("INSERT INTO customer VALUES ('%s', '%s', '%s', '%s', '%s', '%s')", id, name, address, zipCode, city, phone);
		
		try {			
			stmt.execute(statement);
		}
		catch(Exception e) {
			Terminal.say(String.format("An error occured with statment: \n %s", statement), e);
			success = false;
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Could not close statement", e);
		}
		
		
		return success;
	}
	
	public boolean deleteCustomer(String id)
	{
		boolean success = true;
		
		//create statement
		Statement stmt = null;
		
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to delete Customer to DB", e);
			success = false;
		}
		
		try {
			stmt.execute(String.format("DELETE FROM Customer WHERE %s = %s", "CustomerID", id));
		} catch (SQLException e) {
			Terminal.say(String.format("Could not delete Customer ID: %s from database", id), e);
			success = false;
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Could not close statement", e);
		}
		
		
		return success;
	}

	
	public String getHighestID()
	{
		Statement stmt = null;
		
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to delete Customer to DB", e);
		}
		
		try {
			stmt.execute(String.format("SELECT MAX(CAST(%s AS Int)) AS Res FROM Customer", "CustomerID"));
		} catch (SQLException e) {
			Terminal.say("Could not find highest CustomerID", e);
		}
		
		ResultSet resSet = null;
		
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("Could not get ResultSet from getHighestNo", e);
		}
		
		String result = null;
		try {
			resSet.next();
			int hiNo = resSet.getInt("Res");
			result = hiNo + "";
		}
		catch(Exception e) {
			Terminal.say("En Error occured in getHighestNo()", e);
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Could not close statement", e);
		}
		
		return result;
	}
	
	public void updateCustomer(String id, String name, String address, String zipCode, String city, String phoneNo)
	{
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("Create Statement Error", e);
		}
		
		String statement = String.format("UPDATE Customer SET name = '%s', address = '%s', zipCode = '%s', city  = '%s', phoneNo = '%s' FROM Customer WHERE CustomerID = %s",
															  name,   	   address, 	   zipCode, 	   city,	     phoneNo, 									     id);
		try {
			stmt.execute(statement);
			
		} catch (SQLException e) {
			Terminal.say(String.format("Execute statement \n %s", statement), e);
		}
		
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Close Statement error", e);
		}
		
	}
}
